﻿using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Configuration;
using System.Collections.Generic;
using System.Collections;
using PersistentObject.Common.Criteria;
using PersistentObject.SqlServer2012;
using System.Data;
using System.Linq;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using PersistentObject.Common;
using PersistentObject.StudentTest.Entities;

namespace PersistentObject.StudentTest
{
    [TestClass]
    public class SqlExecutorTestInSqlServer2012 : BaseSqlExecutorTest
    {
        static BaseDialect dialect = new SqlServer2012Dialect();
        public override Common.BaseDialect Dialect
        {
            get { return dialect; }
        }

        [TestMethod]
        public void LoadWithNamedColumns()
        {
            var sql = @"
select
[ClassA0].[Id] as ClassA__0__Id,
[ClassA0].[BOOL] as ClassA__0__BOOL,
[ClassA0].[INT] as ClassA__0__INT,
[ClassA0].[STRING] as ClassA__0__STRING,
[ClassA0].[DATETIME] as ClassA__0__DATETIME,
[ClassA0].[DECIMAL] as ClassA__0__DECIMAL,
[ClassA0].[IMAGE] as ClassA__0__IMAGE,
[ClassA0].[ClassBId] as ClassA__0__ClassBId,
[ClassA0].ClassBId2 as ClassA__0__ClassBId2,

[ClassB0].[Id] as ClassB__0__Id,
[ClassB0].[BOOL] as ClassB__0__BOOL,
[ClassB0].[INT] as ClassB__0__INT,
[ClassB0].[STRING] as ClassB__0__STRING,
[ClassB0].[DATETIME] as ClassB__0__DATETIME,
[ClassB0].[DECIMAL] as ClassB__0__DECIMAL,
[ClassB0].[IMAGE] as ClassB__0__IMAGE,

[ClassB1].[Id] as ClassB__1__Id,
[ClassB1].[BOOL] as ClassB__1__BOOL,
[ClassB1].[INT] as ClassB__1__INT,
[ClassB1].[STRING] as ClassB__1__STRING,
[ClassB1].[DATETIME] as ClassB__1__DATETIME,
[ClassB1].[DECIMAL] as ClassB__1__DECIMAL

from [ClassA] [ClassA0]
left join [ClassB] [ClassB0] on [ClassB0].[Id] = [ClassA0].ClassBId
left join [ClassB] [ClassB1] on [ClassB1].[Id] = [ClassA0].ClassBId2
";
            var r1 = Executor.Load<ClassA>(sql, null);
            Assert.AreEqual(r1.Count() > 0, true);

            var ra = r1.First();
            Assert.AreEqual(ra.BOOL == true, true);
            Assert.AreEqual(ra.INT == 10, true);
            Assert.AreEqual(ra.STRING == "A", true);
            Assert.AreEqual(ra.DATETIME == new DateTime(2013, 1, 1), true);
            Assert.AreEqual(ra.DECIMAL == 10.01M, true);

            Assert.AreEqual(ra.ClassBId.BOOL == true, true);
            Assert.AreEqual(ra.ClassBId.INT == ra.ClassBId.Id + 10, true);
            Assert.AreEqual(ra.ClassBId.STRING == "B1", true);
            Assert.AreEqual(ra.ClassBId.DATETIME == new DateTime(2013, 1, 1), true);
            Assert.AreEqual(ra.ClassBId.DECIMAL == ra.ClassBId.Id + 0.01M, true);

            Assert.AreEqual(ra.ClassBId2.BOOL == true, true);
            Assert.AreEqual(ra.ClassBId2.INT == ra.ClassBId2.Id + 10, true);
            Assert.AreEqual(ra.ClassBId2.STRING == "BB1", true);
            Assert.AreEqual(ra.ClassBId2.DATETIME == new DateTime(2013, 1, 1), true);
            Assert.AreEqual(ra.ClassBId2.DECIMAL == ra.ClassBId2.Id + 0.01M, true);
        }

        [TestMethod]
        public void LoadWithDefaultColumns()
        {
            var sql = @"
select
[ClassA0].*,
[ClassB0].*,
[ClassB1].*,
[ClassB2].*,
[ClassB3].*,
[ClassB4].*,
[ClassB5].*

from [ClassA] [ClassA0]
left join [ClassB] [ClassB0] on [ClassB0].[Id] = [ClassA0].ClassBId
left join [ClassB] [ClassB1] on [ClassB1].[Id] = [ClassA0].ClassBId2
left join [ClassB] ClassB2 on ClassB2.Id = ClassB0.ClassBId
left join ClassB ClassB3 on ClassB3.Id = ClassB1.ClassBId
left join ClassB ClassB4 on ClassB4.Id = ClassB2.ClassBId
left join ClassB ClassB5 on ClassB5.Id = ClassB3.ClassBId
";
            var r1 = Executor.Load<ClassA>(sql, null);
            Assert.AreEqual(r1.Count() > 0, true);

            var ra = r1.First();
            Assert.AreEqual(ra.BOOL == true, true);
            Assert.AreEqual(ra.INT == 10, true);
            Assert.AreEqual(ra.STRING == "A", true);
            Assert.AreEqual(ra.DATETIME == new DateTime(2013, 1, 1), true);
            Assert.AreEqual(ra.DECIMAL == 10.01M, true);
            Assert.AreEqual(ra.IMAGE != null, true);
            if (File.Exists(@"d:\logo2.png")) File.Delete(@"d:\logo2.png");
            ra.IMAGE.Save(@"d:\logo2.png", ImageFormat.Png);

            Assert.AreEqual(ra.ClassBId.BOOL == true, true);
            Assert.AreEqual(ra.ClassBId.INT == ra.ClassBId.Id + 10, true);
            Assert.AreEqual(ra.ClassBId.STRING == "B1", true);
            Assert.AreEqual(ra.ClassBId.DATETIME == new DateTime(2013, 1, 1), true);
            Assert.AreEqual(ra.ClassBId.DECIMAL == ra.ClassBId.Id + 0.01M, true);

            Assert.AreEqual(ra.ClassBId2.BOOL == true, true);
            Assert.AreEqual(ra.ClassBId2.INT == ra.ClassBId2.Id + 10, true);
            Assert.AreEqual(ra.ClassBId2.STRING == "BB1", true);
            Assert.AreEqual(ra.ClassBId2.DATETIME == new DateTime(2013, 1, 1), true);
            Assert.AreEqual(ra.ClassBId2.DECIMAL == ra.ClassBId2.Id + 0.01M, true);
        }

        [TestMethod]
        public void SaveNewObject()
        {
            var sql = @"
declare @__count int; set @__count = 0;
select @__count = count(1) 
from [ClassA]
where Id = @ClassA__0__Id;
if(@__Count > 0)
begin
update [ClassA] set
    [BOOL] = @ClassA__0__BOOL,
    [INT] = @ClassA__0__INT,
    [STRING] = @ClassA__0__STRING,
    [DATETIME] = @ClassA__0__DATETIME,
    [DECIMAL] = @ClassA__0__DECIMAL,
    [IMAGE] = @ClassA__0__IMAGE,
    [ClassBId] = @ClassA__0__ClassBId
where Id = @ClassA__0__Id
select @ClassA__0__Id as ClassA__Id
end
else
begin
insert into [ClassA]([BOOL],[INT],[STRING],[DATETIME],[DECIMAL],[IMAGE], [ClassBId])
values(@ClassA__0__BOOL, @ClassA__0__INT, @ClassA__0__STRING, @ClassA__0__DATETIME, @ClassA__0__DECIMAL, @ClassA__0__IMAGE, @ClassA__0__ClassBId)
select cast(@@identity as int) as ClassA__Id
end
";
            ClassA a = new ClassA()
            {
                BOOL = false,
                INT = 20,
                STRING = "BB",
                DATETIME = new DateTime(2014, 1, 1),
                DECIMAL = 20.01M,
                IMAGE = Image.FromFile(@"D:\logo.png"),
                ClassBId = new ClassB()
                {
                    Id = 1,
                }
            };
            var ra = Executor.Save<ClassA>(sql, a);
            Assert.AreEqual(ra.Id > 0, true);
        }

        [TestMethod]
        public void SaveExistObject()
        {
            var sql = @"
declare @__count int; set @__count = 0;
select @__count = count(1) 
from [ClassA]
where Id = @ClassA__0__Id;
if(@__Count > 0)
begin
update [ClassA] set
    [BOOL] = @ClassA__0__BOOL,
    [INT] = @ClassA__0__INT,
    [STRING] = @ClassA__0__STRING,
    [DATETIME] = @ClassA__0__DATETIME,
    [DECIMAL] = @ClassA__0__DECIMAL,
    [IMAGE] = @ClassA__0__IMAGE,
    [ClassBId] = @ClassA__0__ClassBId
where Id = @ClassA__0__Id
select @ClassA__0__Id as ClassA__Id
end
else
begin
insert into [ClassA]([BOOL],[INT],[STRING],[DATETIME],[DECIMAL],[IMAGE], [ClassBId])
values(@ClassA__0__BOOL, @ClassA__0__INT, @ClassA__0__STRING, @ClassA__0__DATETIME, @ClassA__0__DECIMAL, @ClassA__0__IMAGE, @ClassA__0__ClassBId)
select cast(@@identity as int) as ClassA__Id
end
";
            ClassA a = new ClassA()
            {
                Id = 3,
                BOOL = false,
                INT = 20,
                STRING = "CC",
                DATETIME = new DateTime(2014, 1, 1),
                DECIMAL = 20.01M,
                IMAGE = Image.FromFile(@"D:\logo.png"),
                ClassBId = new ClassB()
                {
                    Id = 1,
                }
            };
            var ra = Executor.Save<ClassA>(sql, a);
            Assert.AreEqual(ra.Id > 0, true);
        }

        [TestMethod]
        public void DeleteByScript()
        {
            var sql = @"
delete ClassA__0
from [ClassA] ClassA__0
where Id = @Id;
";
            IDataParameter pa = Dialect.CreateParameter();
            pa.ParameterName = "@Id";
            pa.Value = 4;
            pa.DbType = DbType.Int32;
            List<IDataParameter> list = new List<IDataParameter>();
            list.Add(pa);
            Executor.Delete<ClassA>(sql, list);
        }

        [TestMethod]
        public void DeleteSingleObject()
        {
            var sql = @"
delete ClassA__0
from [ClassA] ClassA__0
where Id = @ClassA__0__Id;
";
            ClassA a = new ClassA()
            {
                Id = 4,
                BOOL = false,
                INT = 20,
                STRING = "BB",
                DATETIME = new DateTime(2014, 1, 1),
                DECIMAL = 20.01M,
                IMAGE = Image.FromFile(@"D:\logo.png"),
                ClassBId = new ClassB()
                {
                    Id = 1,
                }
            };
            var ra = Executor.Delete<ClassA>(sql, a);
            //Assert.AreEqual(ra.Id == 0, true);
        }
    }
}
